﻿using ExcelDna.Integration;
using static ExcelDna.Integration.XlCall;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;

namespace ExcelUDF
{
    public partial class ExcelUDF
    {
        [ExcelFunction(Category = "逻辑判断_文本", Description = "判断查找字符是否在源字符串内，如果存在返回true，否则返回false，示例：lookupValue:AB,sourceString:ABCDE,AB在ABCDE中，结果返回true")]
        public static bool IsTextContains(
            [ExcelArgument(Description = "查找字符串，如ABCDE")] string sourceString,
            [ExcelArgument(Description = "查找条件，如AB")] string lookupValue)
        {
            return sourceString.Contains(lookupValue);
        }

        [ExcelFunction(Category = "逻辑判断_区域", IsMacroType =true,Description = "判断查找区域内查找是否有查找条件相同的，如果存在返回true，否则返回false，示例：lookupValue:ABC,sourceRange:A1：A3｛ABC，BCD，EDF｝,ABC在sourceRange中，结果返回true")]
        public static object IsRangeContains(
                [ExcelArgument(Description = "查找的区域如：A1：A3｛ABC，BCD，EDF｝",AllowReference =true)] object sourceRange,
                [ExcelArgument(Description = "查找条件，如B1的值ABC")] object lookupValue,
                [ExcelArgument(Description = "是否精确匹配，传入0为false,非0为true,默认是true，即为精确匹配，如AB与｛ABC，BCD，EDF｝不匹配")] object IsExactMatch
                )
        {
            Excel(xlfVolatile, false);//取消易失性


            if (sourceRange is ExcelReference && !(lookupValue is ExcelMissing))
            {
                bool matchFlag = true;

                if (IsExactMatch is double && (double)IsExactMatch==0)
                {
                    matchFlag = false;
                }
                else if (IsExactMatch is bool && (bool)IsExactMatch == false)
                {
                    matchFlag = false;
                }

                ExcelReference usedSrcRange = GetUsedSrcRange((ExcelReference)sourceRange);
                object values = usedSrcRange.GetValue();
                if (values is object[,])
                {
                    object[,] usedSrcArr = (object[,])values;
                    for (int i = 0; i < usedSrcArr.GetLength(0); i++)
                    {
                        for (int j = 0; j < usedSrcArr.GetLength(1); j++)
                        {
                            if (matchFlag == true)
                            {
                                //精确匹配用的是数字或字符串，让object自己判断
                                if (usedSrcArr[i, j].Equals(lookupValue))
                                {
                                    return true;
                                }
                            }
                            else
                            {
                                //模糊匹配一定是用字符串匹配
                                if (usedSrcArr[i, j].ToString().Contains(lookupValue.ToString()))
                                {
                                    return true;
                                }
                            }


                        }
                    }
                    return false;
                }
                else
                {
                    return values.Equals(lookupValue);
                }

            }
            else
            {
                return ExcelError.ExcelErrorValue;
            }
           
        }


        [ExcelFunction(Category = "逻辑判断_区域", IsMacroType = true, Description = "判断查找区域内查找是否有至少2个满足查找条件的值，如果存在返回true，否则返回false，示例：lookupValue:ABC,sourceRange:A1：A3｛ABC，ABC，EDF｝,ABC在sourceRange中出现2次，结果返回true")]
        public static object IsRangeContainsDuplicatedValue(
        [ExcelArgument(Description = "查找的区域如：A1：A3｛ABC，ABC，EDF｝", AllowReference = true)] object sourceRange,
        [ExcelArgument(Description = "查找条件，如B1的值ABC")] object lookupValue,
        [ExcelArgument(Description = "是否精确匹配，传入0为false,非0为true,默认是true，即为精确匹配，如AB与｛ABC，ABC，EDF｝不匹配")] object IsExactMatch 
        )
        {
            Excel(xlfVolatile, false);//取消易失性
            byte DuplicatedCount = 0;
            if (sourceRange is ExcelReference && !(lookupValue is ExcelMissing))
            {
                //默认为true
                bool matchFlag = true;
                //只有数字0和逻辑false才赋值matchFlag = false
                if (IsExactMatch is double && (double)IsExactMatch == 0)
                {
                    matchFlag = false;
                }
                else if (IsExactMatch is bool && (bool)IsExactMatch == false)
                {
                    matchFlag = false;
                }

                ExcelReference usedSrcRange = GetUsedSrcRange((ExcelReference)sourceRange);
                object values = usedSrcRange.GetValue();
                if (values is object[,])
                {
                    object[,] usedSrcArr = (object[,])values;
                    for (int i = 0; i < usedSrcArr.GetLength(0); i++)
                    {
                        for (int j = 0; j < usedSrcArr.GetLength(1); j++)
                        {
                            if (matchFlag == true)
                            {
                                //精确匹配用的是数字或字符串，让object自己判断
                                if (usedSrcArr[i, j].Equals(lookupValue))
                                {
                                    DuplicatedCount++;
                                    if (DuplicatedCount>1)
                                    {
                                        return true;
                                    }
                                    
                                }
                            }
                            else
                            {
                                //模糊匹配一定是用字符串匹配
                                if (usedSrcArr[i, j].ToString().Contains(lookupValue.ToString()))
                                {
                                    if (DuplicatedCount > 1)
                                    {
                                        return true;
                                    }
                                    DuplicatedCount++;
                                }
                            }


                        }
                    }
                    return false;
                }
                else
                {
                    return false;
                }

            }
            else
            {
                return false;
            }

        }


        [ExcelFunction(IsMacroType =true,Description = "取源区域与当前usedRange的交集区域")]
        private static ExcelReference GetUsedSrcRange(ExcelReference srcRange)
        {
            object targetSheetName = Excel(xlSheetNm, srcRange);
            //拿到已使用区域的行列序号，从0开始
            int firstRow = (int)(double)Excel(xlfGetDocument, 9, targetSheetName) - 1;
            int lastRow = (int)(double)Excel(xlfGetDocument, 10, targetSheetName) - 1;
            int firstCol = (int)(double)Excel(xlfGetDocument, 11, targetSheetName) - 1;
            int lastCol = (int)(double)Excel(xlfGetDocument, 12, targetSheetName) - 1;
            //类型转换为ExcelReference，前面已经判断过是ExcelReference类型，不是的话程序会退出。
            ExcelReference targetRef = (ExcelReference)srcRange;

            //拿到传入区域和已使用区域之间的交集
            ExcelReference usedTarget = new ExcelReference(
                    rowFirst: firstRow > targetRef.RowFirst ? firstRow : targetRef.RowFirst,
                    rowLast: lastRow < targetRef.RowLast ? lastRow : targetRef.RowLast,
                    columnFirst: firstCol > targetRef.ColumnFirst ? firstCol : targetRef.ColumnFirst,
                    columnLast: lastCol < targetRef.ColumnLast ? lastCol : targetRef.ColumnLast,
                    sheetId: targetRef.SheetId);
            return usedTarget;
        }
       











    }
}
